﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DLL
{
    class DBHelper
    {
        /// <summary>
        /// 连接数据库的连接字符串
        /// </summary>
        private static string strConn = ConfigurationManager.ConnectionStrings["Test3ConnectionString"].ConnectionString;
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sqlparameters">参数列表</param>
        /// <returns>返回查询后table表的数据</returns>
        public static DataTable query(string sql,params SqlParameter [] sqlparameters)
        {
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand comm = new SqlCommand(sql,conn);
            //SqlDataAdapter 将从数据源中获取的数据存放到 DataTable 中
            SqlDataAdapter sda = new SqlDataAdapter(comm);
            //判断可变数组是否有数据(长度大于0)
            if (sqlparameters.Length > 0)
            {
                //若数组有数据，通过command调用集合并添加sqlparameters中的数据
                sda.SelectCommand.Parameters.AddRange(sqlparameters);
            }
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
        /// <summary>
        /// 单条查询（Dataread）
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sqlparameters">参数列表</param>
        /// <returns></returns>
        public static SqlDataReader read(string sql,params SqlParameter[] sqlparameters)
        {
            SqlConnection conn = new SqlConnection(strConn);
            conn.Open();
            //Command在Connection(程序与数据库创立连接)基础上，对数据库发出SQL查询指令
            SqlCommand comm = new SqlCommand(sql, conn);
            //判断可变数组是否有数据(长度大于0)
            if (sqlparameters.Length > 0)
            {
                //若数组有数据，调用集合并添加sqlparameters中的数据
                comm.Parameters.AddRange(sqlparameters);
            }
            //通过dataread读取一行数据（dataread一次读取一行，只进只读）
            SqlDataReader read = comm.ExecuteReader(CommandBehavior.CloseConnection);
            //read.Close();
            return read;
        }
        /// <summary>
        /// 增删改数据
        /// </summary>
        /// <param name="sql">增删改语句</param>
        /// <param name="sqlparameters">参数列表</param>
        /// <returns></returns>
        public static int NonQuery(string sql,CommandType type=CommandType.Text,params SqlParameter[] sqlparameters)
        {
            int num = 0;
            //使用using进行垃圾回收
            using (SqlConnection conn = new SqlConnection(strConn))
            //Command在Connection(程序与数据库创立连接)基础上，对数据库发出SQL 增删改语句 指令
            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                comm.CommandType = type;
                try
                {
                    if (sqlparameters.Length > 0)
                    {
                        //若数组有数据，调用集合并添加sqlparameters中的数据
                        comm.Parameters.AddRange(sqlparameters);
                    }
                    conn.Open();
                    //将执行SQL语句并受影响的行数返回
                    num = comm.ExecuteNonQuery();
                }
                finally
                {
                    //判断sqlconnection的连接状态是否是打开，打开则关闭
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }
            return num;
        }
    }
}
